<?php
/**
* @software         [JiJinCMS]
* @author           [上海集锦信息科技有限公司]
* @website          [http://www.jijinweb.com]
* @email            [sunny@jijinweb.com]
**/
namespace app\admin\controller;
use think\facade\View;
use think\facade\Db;
use think\facade\Request;
use think\facade\Cache;
use think\facade\Env;
class Database extends \app\admin\controller\Admin{
    public function __construct(\think\App $app) {
        parent::__construct($app);
        $this->path=base_path()."data/sqlBake/" ;//备份路径  
        $this->filesize=100;//KB 文件备份大小
        $this->max_num=300;//条 单个文件记录数最多不能超过的条数【防止有的数据较小，都上千条数据了文件大小还没有超过filesize】
        ini_set('memory_limit', '1024M');//这里是将php.ini的内存大小临时修改为比较大的，因为数据备份需要占用比较大的内存
    }   
    //数据库列表
    public function index(){
        $list  = Db::query('SHOW TABLE STATUS');
        $list  = array_map('array_change_key_case', $list);
        View::assign([
            "pagetitle"=>"数据备份",
            "data"=>$list
        ]);
        return View::fetch();
    }
    //数据库备份列表
    public function recovery(){
        $path = realpath($this->path);
        $flag = \FilesystemIterator::KEY_AS_FILENAME;
        $glob = new \FilesystemIterator($path,  $flag);
        $list = array();
        $i=0;
        foreach ($glob as $name => $file) {
            $list[$i]["name"]=$name;
            $list[$i]["size"]=0;
            $list[$i]["file_num"]=0;
            //获取文件数量和大小
            $path2 = realpath($this->path."/".$name."/config.json");
            $text=$this->ReadFiletext($path2);
            $config=  json_decode($text);
            $list[$i]["addtime"]=$config->addtime;
            $list[$i]["file_num"]=$config->file_num;
            $list[$i]["file_size"]=$config->file_size;
            //
            $i++;
        }
        $list=array_reverse($list);//倒叙
        View::assign([
            "data"=>$list,
            "pagetitle"=>"恢复数据"
        ]);
        return View::fetch();
    }
    //字段列表
    public function show_field($table=NULL){
        $list  = Db::query("SHOW FIELDS FROM `".$table."`");
        View::assign([
            "data"=>$list,
            "pagetitle"=>$table
        ]);
        return View::fetch();        
    }


    /**
     * 优化表
     * @param  String $tables 表名
     * @author 麦当苗儿 <zuojiazi@vip.qq.com>
     */
    public function optimize($tables = null){
        if($tables) {
            if(is_array($tables)){
                $tables = implode('`,`', $tables);
                $list = Db::query("OPTIMIZE TABLE `{$tables}`");

                if($list){
                    $this->success("数据表优化完成！");
                } else {
                    $this->error("数据表优化出错请重试！");
                }
            } else {
                $list = Db::query("OPTIMIZE TABLE `{$tables}`");
                if($list){
                    $this->success("数据表'{$tables}'优化完成！");
                } else {
                    $this->error("数据表'{$tables}'优化出错请重试！");
                }
            }
        } else {
            $this->error("请指定要优化的表！");
        }
    }

    /**
     * 修复表
     * @param  String $tables 表名
     * @author 麦当苗儿 <zuojiazi@vip.qq.com>
     */
    public function repair($tables = null){
        if($tables) {
            if(is_array($tables)){
                $tables = implode('`,`', $tables);
                $list = Db::query("REPAIR TABLE `{$tables}`");

                if($list){
                    $this->success("数据表修复完成！");
                } else {
                    $this->error("数据表修复出错请重试！");
                }
            } else {
                $list = Db::query("REPAIR TABLE `{$tables}`");
                if($list){
                    $this->success("数据表'{$tables}'修复完成！");
                } else {
                    $this->error("数据表'{$tables}'修复出错请重试！");
                }
            }
        } else {
            $this->error("请指定要修复的表！");
        }
    }
     //删除备份目录
    public function delete($name){
        if(!$name){
            $this->error("参数错误");
        }
        $dir =$this->path."/".$name."/";
        $this->del_dir($dir);
        $this->UserLogModel->add(Request()->action(),"DatabaseBake",$ids,$this->request->param());  
        $this->success("删除成功！");
    }
    //删除目录
    protected function del_dir($dir){
        $dh=opendir($dir);
        while ($file=readdir($dh)) {
            if($file!="." && $file!="..") {
                $fullpath=$dir."/".$file;
                if(!is_dir($fullpath)) {
                    @unlink($fullpath);
                } else {
                    $this->del_dir($fullpath);
                }
            }
        }
        closedir($dh);
        if(rmdir($dir)){            
            return true;
        } else {
            return false;
        }
    }    
    //初使化备份
    function Start_DoBak(){
        $add=Request::param();
        $tablename=$add['tables'];
        $count=count($tablename);
        if(empty($count))
        {
            $this->error("请选择要备份的数据表");
        }
        $bakpath=$this->path.Env::get('database.database')."_".date("YmdHis")."/";
        Cache::set('bakpath', $bakpath, 3600);//备份的文件路径
        $this->DoMkdir($bakpath);
        //生成说明文件
        $readme=$add['readme'];
        $rfile=$bakpath."readme.txt";
        $readme.="\r\n\r\nBaktime: ".date("Y-m-d H:i:s");
        $this->WriteFiletext_n($rfile,$readme);
        //生成配置文件
        for($i=0;$i<$count;$i++){
            $d_table[$tablename[$i]]=0;
        }
        $string=array(
            "tables"=>$tablename,
            "table_files"=>$d_table,
            "addtime"=>time(),
        );
        $string=  json_encode($string);
        $cfile=$bakpath."config.json";
        $this->WriteFiletext_n($cfile,$string);
        $this->success($tablename);
    }   
    //执行备份
    public function Ebak_BakExe(){
        $dumpsql="";
        $path=Cache::get('bakpath');//备份路径
        $table_index=Request::param("table_index",0,"intval");//当前备份的表对应数据下标
        //当前config.json文件配置信息
        $config=$this->ReadFiletext($path."config.json");
        $config=json_decode($config);
        //
        $talbe_name=$config->tables[$table_index];
        if(!$talbe_name){
            $return["progress"]=100;
            $return["bake_end"]=1;
            $this->UpdateConfig($path);
            $this->success($return);exit;
        }
        //注意，前台js根据bake_end=1来判断是否结束
        $return["bake_end"]=0;
        //
        $s=Request::param("start",0,"intval");//开始记录数
        $p=Request::param("p",0,"intval");//文件后缀
        //记录条数            
        $status_r=$this->GetTotal($talbe_name);
        $num=$status_r['count_rows'];
        //备份数据库结构
        if(!$s){
            $dumpsql.=$this->Ebak_Returnstru($talbe_name);
        }
        //取得数据
        $data=Db::query("select * from `".$talbe_name."` limit $s,".($this->max_num+10));
        //取得字段数
        $field_arr=Db::query("SHOW FIELDS FROM `".$talbe_name."`"); 
        $b=0;
        $now_s=0;//这个主要用来防止有的数据表字段和数据过小，可能会出现条数已经1000条以上了文件大小还没有超过100KB，这样会造成恢复数据的时候浏览器卡死状态
        foreach($data as $r){
            $b=1;
            $now_s++;
            $s++;
            $dumpsql.="\$this->E_D(\"replace into `".$talbe_name."` values(";
            $first=1;
            foreach($field_arr as $field){
                $field = array_change_key_case($field, CASE_LOWER);
                //首字段
                if(empty($first)){
                    $dumpsql.=',';
                }else{
                    $first=0;
                }
                $myi=$i+1;  
                if($r[$field['field']]===NULL){
                    $dumpsql.='NULL';
                }else{
                    $dumpsql.=$this->Ebak_ReSqlFtext($r[$field['field']]);    
                }

            }
            $dumpsql.=");\");\r\n";
            //是否超过限制
            if(strlen($dumpsql)>=$this->filesize*1024||$now_s>$this->max_num){//当文件超过指定大小，或者当前已经循环条数大于500的时候则进入下一个备份
                $p++;
                $sfile=$path."/".$talbe_name."_".$p.".php";
                $dumpsql="<?php \r\n".$dumpsql."\r\n ?>";
                $this->WriteFiletext_n($sfile,$dumpsql);
                //进入下一个文件备份
                $return["progress"]=$this->progress_Bar($config,$table_index,$num,$s);
                $return["start"]=$s;
                $return["p"]=$p;
                $return["table_index"]=$table_index;
                $return['table_name']=$config->tables[$table_index];
                $this->success($return);
                exit;
            }
        }
        //最后一个备份
        if(empty($p)||$b==1)
        {

            $p++;
            $sfile=$path."/".$talbe_name."_".$p.".php";
            $dumpsql="<?php \r\n".$dumpsql."\r\n ?>";
            $this->WriteFiletext_n($sfile,$dumpsql);
        }
        $this->Ebak_RepFilenum($p,$talbe_name,$path);
        //进入下一个表
        $return["progress"]=$this->progress_Bar($config,$table_index,$num,$s);
        $table_index++;
        $return["start"]=0;
        $return["p"]=0;
        $return["table_index"]=$table_index;
        $return['table_name']=$config->tables[$table_index];
        $this->success($return);
    } 
    //返回备份进度条
    protected function progress_Bar($config,$table_index,$total,$start){
        //当前所占第几个表
        $self=($table_index)/count($config->tables)*100;
        if($total){
            $self1=($start/$total)*(1/count($config->tables))*100;    
        }else{
            $self1=0;    
        }
        $progress=sprintf("%.2f",($self+$self1));
        return $progress;
    }
    //字符过虑(addslashes)
    function escape_addsstr($str){
        $str=addslashes($str);
        $str=str_replace('\\\'','\'\'',$str);
        $str=str_replace("\\\\","\\\\\\\\",$str);
        $str=str_replace('$','\$',$str);
        return $str;
    }    
    //字符过虑(db)
    protected function escape_dbstr($str){
        $str=mysql_real_escape_string($str);
        $str=str_replace('\\\'','\'\'',$str);
        $str=str_replace("\\\\","\\\\\\\\",$str);
        $str=str_replace('$','\$',$str);
        return $str;
    }
    //字符过虑
    function escape_str($str){
        global $ebak_set_escapetype;
        if($ebak_set_escapetype==2)//real_escape_string
        {
            $str=$this->escape_dbstr($str);
        }
        else//addslashes
        {
            $str=$this->escape_addsstr($str);
        }
        return $str;
    }
    //返回字段内容
    protected function Ebak_ReSqlFtext($str){
        $restr='\''.$this->escape_str($str).'\'';
        return $restr;
    }    
    //查询记录数
    protected function GetTotal($tbname){
        $total=Db::query("select count(*)as count_rows from ".$tbname.";");
        return $total[0];
    }

    //返回数据库结构
    protected function Ebak_Returnstru($table){
	$dumpsql="\$this->E_D(\"DROP TABLE IF EXISTS `".$table."`;\");\r\n";
	//设置引号
	$usql=Db::execute("SET SQL_QUOTE_SHOW_CREATE=1;");
	//数据表结构
	$arr=Db::query("SHOW CREATE TABLE `$table`;");
        $createArr=array_change_key_case($arr[0], CASE_LOWER);
	$create=str_replace("\"","\\\"",$createArr["create table"]);
	$dumpsql.="\$this->E_C(\"".$create."\");\r\n";
	return $dumpsql;
    }    
    //替换文件数
    protected function Ebak_RepFilenum($p,$table,$path){
        if(empty($p)){$p=0;}
        $file=$path."/config.json";
        $text=$this->ReadFiletext($file);
        $rep1="\"".$table."\":0";
        $rep2="\"".$table."\":".$p;  
        $text=str_replace($rep1,$rep2,$text);
        $this->WriteFiletext_n($file,$text);
    } 
    //更新config文件信息
    protected function UpdateConfig($path){
        if(empty($p)){$p=0;}
        $con_file=$path."/config.json";
        $text=$this->ReadFiletext($con_file);
        $config=  json_decode($text);
        //
        $config->file_size=0;
        $config->file_num=0;
        //获取文件数量和大小
        $path = realpath($path);
        $flag = \FilesystemIterator::KEY_AS_FILENAME;
        $glob = new \FilesystemIterator($path,$flag);
        foreach ($glob as $name => $file) {
            $size=$file->getSize();
            $config->file_size=$config->file_size+$size;
            $config->file_num++;            
        }
        //写入文件
        $file=$path."/config.json";
        $text=  json_encode($config);
        $this->WriteFiletext_n($file,$text);
    }     
    //取得文件内容
    protected function ReadFiletext($filepath){
        $htmlfp=@fopen($filepath,"r");
        while($data=@fread($htmlfp,1000))
        {
                $string.=$data;
        }
        @fclose($htmlfp);
        return $string;
    }    
    //写文件
    protected function WriteFiletext_n($filepath,$string){
        $fp=@fopen($filepath,"w");
        @fputs($fp,$string);
        @fclose($fp);
        if(empty($filechmod))
        {
            @chmod($filepath,0777); 
        }
    }   
    //建立目录函数
    protected function DoMkdir($path){
        //不存在则建立
        if(!file_exists($path))
        {
            $mk=@mkdir($path,0777);
            @chmod($path,0777);
            if(empty($mk))
            {
                $this->error("创建目录失败");
            }
        }
        return true;
    }    
  

    /**
     * 还原数据库
     * @author 麦当苗儿 <zuojiazi@vip.qq.com>
     */
    public function import(){
        $name=Request::param("name");
        if(!$name){
            $this->error("缺少参数");
        }
        $path=$this->path.$name;
        $table_index=Request::param("table_index",0,"intval");
        $p=Request::param("p",1,"intval");        
        //
        $con_file=$path."/config.json";
        $text=$this->ReadFiletext($con_file);
        if(!$text){
            $this->error("目录文件不存在，或者文件缺少");
        }
        $config=  json_decode($text);
        //如果没有表了，正明恢复成功了
        if(!$config->tables[$table_index]){            
            $this->success("ok");exit;
        }
        //循环的当前表名
        $this_table=$config->tables[$table_index];
        //开始恢复数据
        $file=$path."/".$this_table."_".$p.".php";
        include_once($file);
        //进度条
        $return["progress"]=$this->import_progress_Bar($config,$table_index,$config->table_files->$this_table,$p);
        if($return["progress"]==100){
            $this->UserLogModel->add("add","DatabaseBake",0,"备份数据");  
        }
        $return["name"]=$name;
        //如果文件卷标数等于最大的，则进入下一个表
        if($p==$config->table_files->$this_table){
            $return["table_index"]=$table_index+1;
            $return["p"]=1;
        }else{
            $return["table_index"]=$table_index;
            $return["p"]=$p+1;
        }        
        $this->success($return);exit;
    }
    //返回恢复数据的进度条
    protected function import_progress_Bar($config,$table_index,$total_p,$p){
        //当前所占第几个表
        $self=($table_index)/count($config->tables)*100;
        $self1=($p/$total_p)*(1/count($config->tables))*100;     
        $progress=sprintf("%.2f",($self+$self1));
        return $progress;
    }    
    //执行SQL
    protected function E_D($sql){
        Db::execute($sql);
    }
    //建立表
    protected function E_C($sql){
        //替换数据表引擎(因为有的服务器不支持InnoDB有的不支持MyISAM 这里主要是获取当前的默认引擎是什么，然后创建表的时候按这个引擎来创建)
        $sql=preg_replace("/(?i)ENGINE=([a-zA-Z])+ /", "ENGINE=".$this->get_engine()." ", $sql);
        Db::execute($sql);
    }  
    //获取数据库存储类型
    protected function get_engine(){
        $result=Db::query("show variables like '%storage_engine%'");
        $result2 = array_change_key_case($result[0], CASE_LOWER);
        return $result2['value'];
    }
}
